package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import beans.Person;
import beans.Project;

public class ProjectDAO {
	public static List getAssignedProjects(Person person) {
		try {
			Connection c = DataUtils.getDBConnection();
			
			PreparedStatement st = c.prepareStatement(SQL.map.get("SASSIGNEDPROJECTS.sql").toString());
			st.setInt(1, person.id);
			
			ResultSet rs = st.executeQuery();
		    
		    List projectList = new ArrayList();
		    
		    while (rs.next()) {
			    int projId = rs.getInt("PROJ_ID");
			    String title = rs.getString("TITLE");
			    Project project = new Project(projId, title);
			    projectList.add(project);
		    }

		    rs.close();
		    st.close();
		    
		    return projectList;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static Project getTasks(Project project, Person person) {
		try {
			Connection c = DataUtils.getDBConnection();
			
			PreparedStatement st = c.prepareStatement(SQL.map.get("SASSIGNEDTASKS.sql").toString());
			st.setInt(1, project.id);
			st.setInt(2, person.id);
			
			ResultSet rs = st.executeQuery();
		    
		    while (rs.next()) {
			    int taskId = rs.getInt("TASK_ID");
			    String status = rs.getString("STATUS");
			    int sequence = rs.getInt("SEQUENCE");
			    String description = rs.getString("DESCRIPTION");
			    project.tasks.add(project.new Task(taskId, status, sequence, description));
		    }

		    rs.close();
		    st.close();
		    
		    return project;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static String addProject(String title, String pm_id) {
		try {
			Connection c = DataUtils.getDBConnection();
			
			PreparedStatement st = c.prepareStatement(SQL.map.get("IPROJECT.sql").toString());
			st.setString(1, title);
			st.setInt(2, Integer.parseInt(pm_id));
				
			st.executeUpdate();
		
			st.close();
			    
		    return "";
		} catch (SQLException s) {
			int errorCode = s.getErrorCode();
			if (errorCode == 1) {
				return "Project titles must be unique.";
			} else {
				return "" + errorCode;
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public static List getProjectsAndManagers() {
		try {
			Connection c = DataUtils.getDBConnection();
			
			PreparedStatement st = c.prepareStatement(SQL.map.get("SPROJECTS_PMS.sql").toString());
			
			ResultSet rs = st.executeQuery();
		    
			List list = new ArrayList();
			
		    while (rs.next()) {
			    int projId = rs.getInt("PROJ_ID");
			    String title = rs.getString("TITLE");
			    int pid = rs.getInt("PID");
			    String email = rs.getString("EMAIL");
			    Project project = new Project(projId, title, new Person(pid, email, Person.PM));
			    list.add(project);
		    }

		    rs.close();
		    st.close();
		    
		    return list;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		
	}
	
	public static void addTask(int projectId, int seq, String description) {
		try {
			Connection c = DataUtils.getDBConnection();
		
			PreparedStatement st = c.prepareStatement(SQL.map.get("ITASK.sql").toString());
			st.setInt(1, projectId);
			st.setInt(2, seq);
			st.setString(3, description);
			
			st.executeUpdate();
			
		    st.close();
		    
		    return;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

}
